package br.cs.correspodente.DAO;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.persistence.EntityManager;

import br.cs.correspodente.beans.Comprador;
import br.cs.correspodente.conexao.FactoryConnection;
import br.cs.correspodente.interfaces.DAO;
import br.cs.correspodente.util.CompradorUtil;

public class CompradorDAO implements DAO<Comprador>{
	
	private final static String sqlAdd = "INSERT INTO Comprador VALUES(0,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
	private final static String sqlDell = "DELETE FROM Comprador WHERE idComprador = ?;";
	private final static String sqlUp = "UPDATE Comprador SET nome=?, cpf=?,data_nasc=?,endereco=?,conta=?,agencia=?,conjuge=?,naturalidade=?,nacionalidade=?,rg=?,pis=?,renda_formal=?,renda_informal=? WHERE idComprador =?;";
	private final static String sqlAll = "SELECT *FROM Comprador WHERE idComprador = ?;";
	private final static String sqlGetByID = "SELECT *FROM Comprador WHERE idComprador = ?;";
	
	
	@Override
	public void add(Comprador comprador) {
		EntityManager em = FactoryConnection.getInstance().criaConexaoEntity();
		em.getTransaction().begin();
		em.persist(comprador);
		em.flush();
		em.getTransaction().commit();
		em.close();
	}

	@Override
	public void dell(int id) throws SQLException, ClassNotFoundException {
		Connection con = FactoryConnection.getInstance().criaConexao();
		PreparedStatement stm = con.prepareStatement(sqlDell);
		stm.setInt(1,id);
		stm.execute();
		FactoryConnection.getInstance().closeConexao(con, stm);
	}

	@Override
	public void up(Comprador comprador) throws SQLException, ClassNotFoundException {
		Connection con = FactoryConnection.getInstance().criaConexao();
		PreparedStatement stm = con.prepareStatement(sqlUp);
		stm.setString(1, comprador.getNome());
		stm.setString(2, comprador.getCpf());
		stm.setDate(3,new Date(comprador.getData_Nasc().getTime()));
		stm.setString(4, comprador.getEndereco());
		stm.setString(5, comprador.getConta());
		stm.setString(6, comprador.getAgencia());
		stm.setString(7, comprador.getConjuge());
		stm.setString(8, comprador.getNaturalidade());
		stm.setString(9, comprador.getNacionalidade());
		stm.setString(10, comprador.getRg());
		stm.setString(11, comprador.getPis());
		stm.setFloat(12, comprador.getRenda_Formal());
		stm.setFloat(13, comprador.getRenda_informal());
		stm.setInt(14, comprador.getId());
		stm.executeUpdate();
		FactoryConnection.getInstance().closeConexao(con, stm);
		
	}

	@Override
	public ArrayList<Comprador> getAll() throws SQLException,
			ClassNotFoundException {
		Connection con = FactoryConnection.getInstance().criaConexao();
		PreparedStatement stm = con.prepareStatement(sqlAll);
		ResultSet rs = stm.executeQuery();
		ArrayList<Comprador> compradores = new ArrayList<Comprador>();
		while(rs.next()){
			compradores.add(CompradorUtil.novoComprador(rs));
		}
		FactoryConnection.getInstance().closeConexao(con, stm);
		return compradores;
	}

	@Override
	public Comprador getById(int id) throws SQLException,
			ClassNotFoundException {
		
		Connection con = FactoryConnection.getInstance().criaConexao();
		PreparedStatement stm = con.prepareStatement(sqlGetByID);
		stm.setInt(1, id);
		ResultSet rs = stm.executeQuery();
		Comprador comprador = new Comprador();
		while(rs.next()){
			comprador = CompradorUtil.novoComprador(rs);
		}
		FactoryConnection.getInstance().closeConexao(con, stm);
		return comprador;
	}
	
}
